'''
Created on 2017年5月16日
@author: zhangxue
'''
import urllib.request as ur
import json
import pymysql
import sys

url = "http://account.zx1989.top/ip.txt"

# 1.打开URL连接，timeout设置超时时间，单位ms
data = ur.urlopen(url = url, timeout = 1000)

# 2.返回http.client.HTTPResponse 结果类型
print(type(data))

# 3.http://www.itcast.cn/
print(data.geturl())

# 4.头信息 http.client.HTTPMessage
# Date: Tue, 16 May 2017 13:51:54 GMT
# Server: Apache/2.4.23 (Win64) OpenSSL/1.0.2h PHP/5.6.28
# Accept-Ranges: bytes
# Connection: close
# Transfer-Encoding: chunked
# Content-Type: text/html
print(data.info())

# 5.响应码
print('request_status:', data.getcode())


# 6.将对象转为utf-8编码的字符串
html = data.read().decode("utf-8").replace('\'', '\"')

# 7.将数据转为json
json_list = json.loads(html)

print("获得数据", json_list)
# 8.打开数据库连接
db = pymysql.connect(host = "127.0.0.1", port = 3306, user = "root", password = "root", database = "accountbook")
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# SQL 插入语句
sql = """insert into zx1989_pv_uv(date,
         domain, pv, uv)
         values (%s, %s, %s, %s) 
         ON DUPLICATE  KEY UPDATE pv=VALUES(pv), uv=VALUES(uv);
      """

for item in json_list:
    try:
        # 执行sql语句
        cursor.execute(sql, (item['date'], 'zx1989.top', int(item['pv']), int(item['uv'])))
        # 提交到数据库执行
        db.commit()
    except:
        # 如果发生错误则回滚
        db.rollback()
        print("Unexpected error:", sys.exc_info()[0])

# 关闭数据库连接
db.close()
print("执行完成")